package com.fit.utils.se;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.*;


/**
 * Excel工具,包含了读取Excel和生成excel(生成的Excel为了高效率,故取消了样式)
 * @author hcl
 *
 */
public class ExcelUtil {
	
	private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
	
	/**
	 * 默认的分批次读取数量
	 */
	public static final int INIT_READ_BATCHES_NUM = 200;
	
	/**
	 * 写出Excel时,缓存的条数
	 */
	public static final Integer CACHE_ROW_NUM = 500;


	/**
	 * @describe: 单元格最大的长度
	 */
	public static final Integer CELL_MAX_LENGTH = 32767;
	
	/**
	 * 读取excel,将excel转换为List<List<Record<String>>>集合
	 * @param in 输入流
	 * @param sheetIndex shee下标0开始,如果为负数，表示读取全部sheet
	 * @param startRowIndex 开始的行下标0开始(可为空)
	 * @param maxCellIndex 最大的列下标1开始(可为空)
	 * @return
	 */
	public static List<List<Record<String>>> readExcel(InputStream in, int sheetIndex, Integer startRowIndex, Integer maxCellIndex){
		List<List<Record<String>>> result = new ArrayList<List<Record<String>>>();
		if(EmptyUtils.isNotEmpty(in)){
			try{
				startRowIndex = (EmptyUtils.isNotEmpty(startRowIndex) && startRowIndex >= 0) ? startRowIndex : 0;
				boolean maxCellIndexFlag = (EmptyUtils.isEmpty(maxCellIndex) || maxCellIndex < 0) ? false : true;
				Workbook wb = WorkbookFactory.create(in);
				FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
				//是否遍历sheet
				int totalSheetNum = sheetIndex < 0 ? wb.getNumberOfSheets() : 1;
				sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
				do{
					Sheet sheet = wb.getSheetAt(sheetIndex);//获取工作表
					for(int startRowNum = startRowIndex,endRowNum = sheet.getLastRowNum(); startRowNum <= endRowNum; startRowNum++){
						Row row = sheet.getRow(startRowNum);
						List<Record<String>> cellList = new ArrayList<Record<String>>();
						if(EmptyUtils.isNotEmpty(row)){
							int lastCellIndex = maxCellIndexFlag ? maxCellIndex : row.getLastCellNum();
							for(int firstCellIndex = 0;firstCellIndex < lastCellIndex; firstCellIndex++){
								Cell cell = row.getCell(firstCellIndex);
								cellList.add(ExcelUtil.getCellToRecord(cell, null, evaluator));
							}
						}
						result.add(cellList);
					}
				}while(++sheetIndex < totalSheetNum);
			}catch(Exception e){
				log.error("读取Excel异常！", e);
				throw new RuntimeException("读取Excel异常！",e);
			}finally{
				IOUtil.close(in);
			}
		}
		return result;
	}
	
	
	
	
	/**
	 * 分批次的读取eaxel
	 * @param in 输入流
	 * @param sheetIndex shee下标0开始,如果为负数，表示读取全部sheet
	 * @param startRowIndex 开始的行下标0开始(可为空)
	 * @param maxCellIndex 最大的列下标1开始(可为空)
	 * @param readBatches 分批次读取接口,调用者要实现此接口
	 * @param readBatchesNum 分批读取数量
	 * @return 读取的总数量
	 */
	public static int readBatchesExcel(InputStream in,int sheetIndex,Integer startRowIndex,Integer maxCellIndex,ReadBatches readBatches,int readBatchesNum){
		int readRowNum = 0;
		if(EmptyUtils.isNotEmpty(in) && EmptyUtils.isNotEmpty(readBatches)){
			try{
				startRowIndex = (EmptyUtils.isNotEmpty(startRowIndex) && startRowIndex >= 0) ? startRowIndex : 0;
				readBatchesNum = readBatchesNum < 1 ? INIT_READ_BATCHES_NUM : readBatchesNum;
				boolean maxCellIndexFlag = (EmptyUtils.isEmpty(maxCellIndex) || maxCellIndex < 0) ? false : true;
				Workbook wb = WorkbookFactory.create(in);
				FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
				
				//是否遍历sheet
				int totalSheetNum = sheetIndex < 0 ? wb.getNumberOfSheets() : 1;
				sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
				do{
					Sheet sheet = wb.getSheetAt(sheetIndex);//获取工作表
					List<List<Record<String>>> result = new ArrayList<List<Record<String>>>();
					int totalRowNum = sheet.getLastRowNum()+1;
					int sheetReadRowNum = 0;
					for(int startRowNum = startRowIndex,endRowNum = sheet.getLastRowNum(); startRowNum <= endRowNum; startRowNum++){
						readRowNum++;
						sheetReadRowNum++;
						Row row = sheet.getRow(startRowNum);
						List<Record<String>> cellList = new ArrayList<Record<String>>();
						if(EmptyUtils.isNotEmpty(row)){
							int lastCellIndex = maxCellIndexFlag ? maxCellIndex : row.getLastCellNum();
							for(int firstCellIndex = 0;firstCellIndex < lastCellIndex; firstCellIndex++){
								Cell cell = row.getCell(firstCellIndex);
								cellList.add(ExcelUtil.getCellToRecord(cell, null, evaluator));
							}
						}
						result.add(cellList);
						if(readRowNum % readBatchesNum == 0 || startRowNum == endRowNum){
							boolean cont = readBatches.read(result, sheetReadRowNum - result.size() + startRowIndex + 1, totalRowNum, sheetIndex + 1,totalSheetNum);
							result.clear();
							if(!cont){
								break;
							}
						}
					}
				}while(++sheetIndex < totalSheetNum);
			}catch(Exception e){
				log.error("读取Excel异常！", e);
				throw new RuntimeException("读取Excel异常！",e);
			}finally{
				IOUtil.close(in);
			}
		}
		return readRowNum;
	}

	
	
	/**
	 * 读取单元格的值
	 * @param cell 单元格对象
	 * @param cellType 单元格类型(可为空)
	 * @param evaluator 获取公式文本类型对象
	 * @return
	 */
	private static Record<String> getCellToRecord(Cell cell, CellType cellType, FormulaEvaluator evaluator){
		Record<String> result = new Record<String>(null);
		if(EmptyUtils.isNotEmpty(cell)){
			cellType = EmptyUtils.isEmpty(cellType) ? cell.getCellTypeEnum() : cellType;
			if(cellType == CellType.STRING){//字符串
				result = new Record<String>(cell.getStringCellValue());
			}else if(cellType == CellType.NUMERIC){
				if(DateUtil.isCellDateFormatted(cell)){//日期
					result = new Record<String>(TimeUtil.getDate(cell.getDateCellValue(), TimeUtil.YMDHMS));
				}else{//数字
					Double douVal = cell.getNumericCellValue();
					result = new Record<String>(StringUtil.toString(douVal));
				}
			}else if(cellType == CellType.FORMULA){//公式
//				result = new Record<String>(cell.getCellFormula());
				if(EmptyUtils.isNotEmpty(evaluator)){
					result = getCellToRecord(cell, evaluator.evaluateFormulaCellEnum(cell), evaluator);
				}
			}else if(cellType == CellType.BLANK){//空白
				result = new Record<String>(null);
			}else if(cellType == CellType.BOOLEAN){//boolean
				result = new Record<String>(cell.getBooleanCellValue()+"");
			}else if(cellType == CellType.ERROR){
				log.error("读取excel列故障！");
				throw new RuntimeException("读取excel列故障！");
			}
		}
		return result;
	}
	
	
	/**
	 * 分批次读取excel接口
	 * @author hcl
	 *
	 */
	public static interface ReadBatches{
		/**
		 * 分批读取接口
		 * @param data 每批数据
		 * @param startRowNum 开始的行号
		 * @param totalRowNum 当前sheet总共的行号
		 * @param sheetIndex 当前正在读取的sheet索引,从1开始
		 * @param sheetTotal 要读取的sheet总数
		 * @return 返回false 停止读取
		 */
		public boolean read(List<List<Record<String>>> data, int startRowNum, int totalRowNum, int sheetIndex, int sheetTotal);
	}










	/**
	 * 将数据写入excel
	 * @param out 输出流
	 * @param title 标题(可为空)
	 * @param rowTitle 行标题(可为空)
	 * @param dataList 数据集合
	 * @param excelType Excel 类型
	 */
	public static void writeExcel(OutputStream out,String title,List<String> rowTitle,List<List<Object>> dataList,ExcelType excelType){
		if(EmptyUtils.isNotEmpty(out)){
			try{
				// 声明一个工作薄
				Workbook workbook = null;
				if(ExcelType.XLS == excelType){
					//office 2003
					workbook = new HSSFWorkbook();
				}else if(ExcelType.XLSX == excelType){
					//office 2007以上
					@SuppressWarnings("resource")
					SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ExcelUtil.CACHE_ROW_NUM);//缓存
					sxssfWorkbook.setCompressTempFiles(true);
					workbook = sxssfWorkbook;
				}
				//查询出一共有多少列
				int columnsNum = 0;
				if(EmptyUtils.isNotEmpty(rowTitle)){
					columnsNum = rowTitle.size();
				}else{
					if(EmptyUtils.isNotEmpty(dataList)){
						List<Object> row1 = dataList.get(0);
						columnsNum = EmptyUtils.isNotEmpty(row1) ? row1.size() : 0;
					}
				}
				if(EmptyUtils.isNotEmpty(workbook)){
					try{
						int titleOccupyRowNum = 0;
						if(EmptyUtils.isNotEmpty(title)){
							titleOccupyRowNum++;
						}
						if(EmptyUtils.isNotEmpty(rowTitle)){
							titleOccupyRowNum++;
						}
						Sheet sheet = ExcelUtil.createSheet(workbook, title, rowTitle, columnsNum);
						int rowIndex = titleOccupyRowNum;

						for(List<Object> rowDate : dataList){
							if(rowIndex >= excelType.getMaxRowNum()){
								//自动计算宽度,Excel 2007处理慢
//								for (int i = 0; i < columnsNum; i++) {
//									sheet.autoSizeColumn(i);
//								}
								sheet = ExcelUtil.createSheet(workbook, title, rowTitle, columnsNum);
								rowIndex = titleOccupyRowNum;
							}
							Row row = sheet.createRow(rowIndex);
							int cellIndex = 0;
							for(Object obj : rowDate){
                                ExcelUtil.setCellValue(obj,row.createCell(cellIndex++),null);
							}
							//每当行数达到设置的值就刷新数据到硬盘,以清理内存
							if(sheet instanceof SXSSFSheet && rowIndex% ExcelUtil.CACHE_ROW_NUM == 0){
								((SXSSFSheet)sheet).flushRows();
							}
							rowIndex++;
						}
						//自动计算宽度,Excel 2007处理慢
//						for (int i = 0; i < columnsNum; i++) {
//							sheet.autoSizeColumn(i);
//						}
					}finally{
						workbook.write(out);
						IOUtil.close(workbook);
						if(EmptyUtils.isNotEmpty(workbook) && workbook instanceof SXSSFWorkbook){
							((SXSSFWorkbook)workbook).dispose();
						}
					}
				}
			}catch (Exception e) {
				log.error("写出Excel异常！", e);
				throw new RuntimeException("写出Excel异常！",e);
			} finally{
				IOUtil.close(out);
			}
		}
	}



	/**
	 * 将数据分批次的写入excel
	 * @param out 输出流
	 * @param title 标题(可为空)
	 * @param rowTitle 行标题(可为空)
	 * @param writeBatches 分批次写出数据接口
	 * @param excelType Excel 类型
	 */
	public static void writeBatchesExcel(OutputStream out,String title,List<String> rowTitle,WriteBatches writeBatches, ExcelType excelType){
		if(EmptyUtils.isNotEmpty(out) && EmptyUtils.isNotEmpty(writeBatches)){
			try{
				// 声明一个工作薄
				Workbook workbook = null;
				if(ExcelType.XLS == excelType){
					//office 2003
					workbook = new HSSFWorkbook();
				}else if(ExcelType.XLSX == excelType){
					//office 2007以上
					@SuppressWarnings("resource")
					SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ExcelUtil.CACHE_ROW_NUM);//缓存
					sxssfWorkbook.setCompressTempFiles(true);
					workbook = sxssfWorkbook;
				}
				if(EmptyUtils.isNotEmpty(workbook)){
					try{
						int titleOccupyRowNum = 0;
						if(EmptyUtils.isNotEmpty(title)){
							titleOccupyRowNum++;
						}
						if(EmptyUtils.isNotEmpty(rowTitle)){
							titleOccupyRowNum++;
						}

						int pageNum = 1;
						int startNum = 0;
						List<List<Object>> dataList = writeBatches.write(pageNum++, startNum);
						if(EmptyUtils.isNotEmpty(dataList)){
							//查询出一共有多少列
							int columnsNum = 0;
							if(EmptyUtils.isNotEmpty(rowTitle)){
								columnsNum = rowTitle.size();
							}else{
								if(EmptyUtils.isNotEmpty(dataList)){
									List<Object> row1 = dataList.get(0);
									columnsNum = EmptyUtils.isNotEmpty(row1) ? row1.size() : 0;
								}
							}
							Sheet sheet = ExcelUtil.createSheet(workbook, title, rowTitle, columnsNum);
							int rowIndex = titleOccupyRowNum;
							do{
								for(List<Object> rowDate : dataList){
									if(rowIndex >= excelType.getMaxRowNum()){
										//自动计算宽度,Excel 2007处理慢
//										for (int i = 0; i < columnsNum; i++) {
//											sheet.autoSizeColumn(i);
//										}
										sheet = ExcelUtil.createSheet(workbook, title, rowTitle, columnsNum);
										rowIndex = titleOccupyRowNum;
									}
									Row row = sheet.createRow(rowIndex);
									int cellIndex = 0;
									for(Object obj : rowDate){
                                        ExcelUtil.setCellValue(obj,row.createCell(cellIndex++),null);
									}
									//每当行数达到设置的值就刷新数据到硬盘,以清理内存
									if(sheet instanceof SXSSFSheet && rowIndex% ExcelUtil.CACHE_ROW_NUM == 0){
										((SXSSFSheet)sheet).flushRows();
									}
									rowIndex++;
								}
							}while(EmptyUtils.isNotEmpty(dataList = writeBatches.write(pageNum++, startNum += dataList.size())));

						}
						//自动计算宽度,Excel 2007处理慢
//						for (int i = 0; i < columnsNum; i++) {
//							sheet.autoSizeColumn(i);
//						}
					}finally{
						workbook.write(out);
						IOUtil.close(workbook);
						if(EmptyUtils.isNotEmpty(workbook) && workbook instanceof SXSSFWorkbook){
							((SXSSFWorkbook)workbook).dispose();
						}
					}
				}
			}catch (Exception e) {
				log.error("写出Excel异常！", e);
				throw new RuntimeException("写出Excel异常！",e);
			} finally{
				IOUtil.close(out);
			}
		}
	}




	/**
	 * 将数据写入excel,可以根据rowTitle调整列的顺序。
	 * @param out 输出流
	 * @param title 标题(可为空)
	 * @param rowTitle 行标题
	 * @param dataList 数据集合
	 * @param excelType Excel 类型
	 */
	public static void writeExcelOrder(OutputStream out,String title,List<ExcelRowTitle> rowTitle,List<Map<String,Object>> dataList,ExcelType excelType){
		if(EmptyUtils.isNotEmpty(out) && EmptyUtils.isNotEmpty(rowTitle)){
			try{
				// 声明一个工作薄
				Workbook workbook = null;
				if(ExcelType.XLS == excelType){
					//office 2003
					workbook = new HSSFWorkbook();
				}else if(ExcelType.XLSX == excelType){
					//office 2007以上
					@SuppressWarnings("resource")
					SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ExcelUtil.CACHE_ROW_NUM);//缓存
					sxssfWorkbook.setCompressTempFiles(true);
					workbook = sxssfWorkbook;
				}
				//查询出一共有多少列
				int columnsNum = rowTitle.size();

				if(EmptyUtils.isNotEmpty(workbook)){
					try{
						int titleOccupyRowNum = 0;
						if(EmptyUtils.isNotEmpty(title)){
							titleOccupyRowNum++;
						}
						if(EmptyUtils.isNotEmpty(rowTitle)){
							titleOccupyRowNum++;
						}
						List<String> rowTitleArr = new ArrayList<String>();
						for(ExcelRowTitle excelRowTitle : rowTitle){
							rowTitleArr.add(excelRowTitle.getTitleName());
						}
						Sheet sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
						int rowIndex = titleOccupyRowNum;
						for(Map<String,Object> rowDate : dataList){
							if(rowIndex >= excelType.getMaxRowNum()){
								sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
								rowIndex = titleOccupyRowNum;
							}
							Row row = sheet.createRow(rowIndex);
							int cellIndex = 0;
							for(ExcelRowTitle excelRowTitle : rowTitle){
								Object obj = rowDate.get(excelRowTitle.getTitleKey());
                                ExcelUtil.setCellValue(obj,row.createCell(cellIndex++),excelRowTitle);
							}
							//每当行数达到设置的值就刷新数据到硬盘,以清理内存
							if(sheet instanceof SXSSFSheet && rowIndex% ExcelUtil.CACHE_ROW_NUM == 0){
								((SXSSFSheet)sheet).flushRows();
							}
							rowIndex++;
						}
					}finally{
						workbook.write(out);
						IOUtil.close(workbook);
						if(EmptyUtils.isNotEmpty(workbook) && workbook instanceof SXSSFWorkbook){
							((SXSSFWorkbook)workbook).dispose();
						}
					}
				}
			}catch (Exception e) {
				log.error("写出Excel异常！", e);
				throw new RuntimeException("写出Excel异常！",e);
			} finally{
				IOUtil.close(out);
			}
		}
	}

	/**
	 * 将数据写入excel,可以根据rowTitle调整列的顺序.dataList中的数据必须遵循了bean规范
	 * @param out out 输出流
	 * @param title 标题(可为空)
	 * @param rowTitle 行标题
	 * @param dataList 遵循了bean规范的数据集合
	 * @param excelType Excel 类型
	 */
	public static void writeExcelOrderByModel(OutputStream out,String title,List<ExcelRowTitle> rowTitle,List<? extends Object> dataList,ExcelType excelType){
		if(EmptyUtils.isNotEmpty(out) && EmptyUtils.isNotEmpty(rowTitle)){
			try{
				// 声明一个工作薄
				Workbook workbook = null;
				if(ExcelType.XLS == excelType){
					//office 2003
					workbook = new HSSFWorkbook();
				}else if(ExcelType.XLSX == excelType){
					//office 2007以上
					@SuppressWarnings("resource")
					SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ExcelUtil.CACHE_ROW_NUM);//缓存
					sxssfWorkbook.setCompressTempFiles(true);
					workbook = sxssfWorkbook;
				}
				//查询出一共有多少列
				int columnsNum = rowTitle.size();
				if(EmptyUtils.isNotEmpty(workbook)){
					try{
						int titleOccupyRowNum = 0;
						if(EmptyUtils.isNotEmpty(title)){
							titleOccupyRowNum++;
						}
						if(EmptyUtils.isNotEmpty(rowTitle)){
							titleOccupyRowNum++;
						}
						List<String> rowTitleArr = new ArrayList<String>();
						for(ExcelRowTitle excelRowTitle : rowTitle){
							rowTitleArr.add(excelRowTitle.getTitleName());
						}
						Sheet sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
						int rowIndex = titleOccupyRowNum;

						Map<String,Method> methodMap = new HashMap<String,Method>();
						if(EmptyUtils.isNotEmpty(dataList)){
							Object data = dataList.get(0);
							if(EmptyUtils.isNotEmpty(data)){
								for(ExcelRowTitle excelRowTitle : rowTitle){
									methodMap.put(excelRowTitle.getTitleKey(), ReflectUtil.getMethodGet(data.getClass(), excelRowTitle.getTitleKey()));
								}
							}
						}
						for(Object rowDate : dataList){
							if(rowIndex >= excelType.getMaxRowNum()){
								sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
								rowIndex = titleOccupyRowNum;
							}
							Row row = sheet.createRow(rowIndex);
							int cellIndex = 0;

							for(ExcelRowTitle excelRowTitle : rowTitle){
								Object obj = ReflectUtil.executeMethod(rowDate, methodMap.get(excelRowTitle.getTitleKey()));
                                ExcelUtil.setCellValue(obj,row.createCell(cellIndex++),excelRowTitle);
							}
							//每当行数达到设置的值就刷新数据到硬盘,以清理内存
							if(sheet instanceof SXSSFSheet && rowIndex% ExcelUtil.CACHE_ROW_NUM == 0){
								((SXSSFSheet)sheet).flushRows();
							}
							rowIndex++;
						}
					}finally{
						workbook.write(out);
						IOUtil.close(workbook);
						if(EmptyUtils.isNotEmpty(workbook) && workbook instanceof SXSSFWorkbook){
							((SXSSFWorkbook)workbook).dispose();
						}
					}
				}
			}catch (Exception e) {
				log.error("写出Excel异常！", e);
				throw new RuntimeException("写出Excel异常！",e);
			} finally{
				IOUtil.close(out);
			}
		}
	}


	/**
	 * 将数据分批次的写入excel,可以根据rowTitle调整列的顺序。
	 * @param out 输出流
	 * @param title 标题(可为空)
	 * @param rowTitle 行标题
	 * @param writeBatchesOrder 分批次写出数据接口
	 * @param excelType Excel 类型
	 */
	public static void writeBatchesExcelOrder(OutputStream out,String title,List<ExcelRowTitle> rowTitle,WriteBatchesOrder writeBatchesOrder, ExcelType excelType){
		if(EmptyUtils.isNotEmpty(out) && EmptyUtils.isNotEmpty(rowTitle) && EmptyUtils.isNotEmpty(writeBatchesOrder)){
			try{
				// 声明一个工作薄
				Workbook workbook = null;
				if(ExcelType.XLS == excelType){
					//office 2003
					workbook = new HSSFWorkbook();
				}else if(ExcelType.XLSX == excelType){
					//office 2007以上
					@SuppressWarnings("resource")
					SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ExcelUtil.CACHE_ROW_NUM);//缓存
					sxssfWorkbook.setCompressTempFiles(true);
					workbook = sxssfWorkbook;
				}
				if(EmptyUtils.isNotEmpty(workbook)){
					try{
						int titleOccupyRowNum = 0;
						if(EmptyUtils.isNotEmpty(title)){
							titleOccupyRowNum++;
						}
						if(EmptyUtils.isNotEmpty(rowTitle)){
							titleOccupyRowNum++;
						}

						int pageNum = 1;
						int startNum = 0;
						List<Map<String,Object>> dataList = writeBatchesOrder.write(pageNum++, startNum);
						if(EmptyUtils.isNotEmpty(dataList)){
							//查询出一共有多少列
							int columnsNum = rowTitle.size();

							List<String> rowTitleArr = new ArrayList<String>();
							for(ExcelRowTitle excelRowTitle : rowTitle){
								rowTitleArr.add(excelRowTitle.getTitleName());
							}


							Sheet sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
							int rowIndex = titleOccupyRowNum;
							do{
								for(Map<String,Object> rowDate : dataList){
									if(rowIndex >= excelType.getMaxRowNum()){
										//自动计算宽度,Excel 2007处理慢
//										for (int i = 0; i < columnsNum; i++) {
//											sheet.autoSizeColumn(i);
//										}
										sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
										rowIndex = titleOccupyRowNum;
									}
									Row row = sheet.createRow(rowIndex);
									int cellIndex = 0;

									for(ExcelRowTitle excelRowTitle : rowTitle){
										Object obj = rowDate.get(excelRowTitle.getTitleKey());
                                        ExcelUtil.setCellValue(obj,row.createCell(cellIndex++),excelRowTitle);
									}
									//每当行数达到设置的值就刷新数据到硬盘,以清理内存
									if(sheet instanceof SXSSFSheet && rowIndex% ExcelUtil.CACHE_ROW_NUM == 0){
										((SXSSFSheet)sheet).flushRows();
									}
									rowIndex++;
								}
							}while(EmptyUtils.isNotEmpty(dataList = writeBatchesOrder.write(pageNum++, startNum += dataList.size())));
						}
						//自动计算宽度,Excel 2007处理慢
//						for (int i = 0; i < columnsNum; i++) {
//							sheet.autoSizeColumn(i);
//						}
					}finally{
						workbook.write(out);
						IOUtil.close(workbook);
						if(EmptyUtils.isNotEmpty(workbook) && workbook instanceof SXSSFWorkbook){
							((SXSSFWorkbook)workbook).dispose();
						}
					}
				}
			}catch (Exception e) {
				log.error("写出Excel异常！", e);
				throw new RuntimeException("写出Excel异常！",e);
			} finally{
				IOUtil.close(out);
			}
		}
	}


	/**
	 * 将数据分批次的写入excel,可以根据rowTitle调整列的顺序。writeBatchesOrderByModel中的数据必须遵循了bean规范
	 * @param out 输出流
	 * @param title 标题(可为空)
	 * @param rowTitle 行标题
	 * @param writeBatchesOrderByModel 分批次写出数据接口,获取的数据必须遵循了bean规范。
	 * @param excelType Excel 类型
	 */
	public static void writeBatchesExcelOrderByModel(OutputStream out,String title,List<ExcelRowTitle> rowTitle,WriteBatchesOrderByModel writeBatchesOrderByModel,ExcelType excelType){
		if(EmptyUtils.isNotEmpty(out) && EmptyUtils.isNotEmpty(rowTitle) && EmptyUtils.isNotEmpty(writeBatchesOrderByModel)){
			try{
				// 声明一个工作薄
				Workbook workbook = null;
				if(ExcelType.XLS == excelType){
					//office 2003
					workbook = new HSSFWorkbook();
				}else if(ExcelType.XLSX == excelType){
					//office 2007以上
					@SuppressWarnings("resource")
					SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ExcelUtil.CACHE_ROW_NUM);//缓存
					sxssfWorkbook.setCompressTempFiles(true);
					workbook = sxssfWorkbook;
				}
				if(EmptyUtils.isNotEmpty(workbook)){
					try{
						int titleOccupyRowNum = 0;
						if(EmptyUtils.isNotEmpty(title)){
							titleOccupyRowNum++;
						}
						if(EmptyUtils.isNotEmpty(rowTitle)){
							titleOccupyRowNum++;
						}

						int pageNum = 1;
						int startNum = 0;
						List<? extends Object> dataList = writeBatchesOrderByModel.write(pageNum++, startNum);
						if(EmptyUtils.isNotEmpty(dataList)){
							//查询出一共有多少列
							int columnsNum = rowTitle.size();

							List<String> rowTitleArr = new ArrayList<String>();
							for(ExcelRowTitle excelRowTitle : rowTitle){
								rowTitleArr.add(excelRowTitle.getTitleName());
							}

							Map<String,Method> methodMap = new HashMap<String,Method>();
							if(EmptyUtils.isNotEmpty(dataList)){
								Object data = dataList.get(0);
								if(EmptyUtils.isNotEmpty(data)){
									for(ExcelRowTitle excelRowTitle : rowTitle){
										methodMap.put(excelRowTitle.getTitleKey(), ReflectUtil.getMethodGet(data.getClass(), excelRowTitle.getTitleKey()));
									}
								}
							}

							Sheet sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
							int rowIndex = titleOccupyRowNum;
							do{
								for(Object rowDate : dataList){
									if(rowIndex >= excelType.getMaxRowNum()){
										//自动计算宽度,Excel 2007处理慢
//										for (int i = 0; i < columnsNum; i++) {
//											sheet.autoSizeColumn(i);
//										}
										sheet = ExcelUtil.createSheet(workbook, title, rowTitleArr, columnsNum);
										rowIndex = titleOccupyRowNum;
									}
									Row row = sheet.createRow(rowIndex);
									int cellIndex = 0;

									for(ExcelRowTitle excelRowTitle : rowTitle){
										Object obj = ReflectUtil.executeMethod(rowDate, methodMap.get(excelRowTitle.getTitleKey()));
										ExcelUtil.setCellValue(obj,row.createCell(cellIndex++),excelRowTitle);
									}
									//每当行数达到设置的值就刷新数据到硬盘,以清理内存
									if(sheet instanceof SXSSFSheet && rowIndex% ExcelUtil.CACHE_ROW_NUM == 0){
										((SXSSFSheet)sheet).flushRows();
									}
									rowIndex++;
								}
							}while(EmptyUtils.isNotEmpty(dataList = writeBatchesOrderByModel.write(pageNum++, startNum += dataList.size())));
						}
						//自动计算宽度,Excel 2007处理慢
//						for (int i = 0; i < columnsNum; i++) {
//							sheet.autoSizeColumn(i);
//						}
					}finally{
						workbook.write(out);
						IOUtil.close(workbook);
						if(EmptyUtils.isNotEmpty(workbook) && workbook instanceof SXSSFWorkbook){
							((SXSSFWorkbook)workbook).dispose();
						}
					}
				}
			}catch (Exception e) {
				log.error("写出Excel异常！", e);
				throw new RuntimeException("写出Excel异常！",e);
			} finally{
				IOUtil.close(out);
			}
		}
	}





	/**
	 * 根据Workbook创建一个新的sheet
	 * @param workbook 工作薄对象
	 * @param title 标题
	 * @param rowTitle 行标题
	 * @param columnsNum 列数量
	 * @return
	 */
	private static Sheet createSheet(Workbook workbook,String title,List<String> rowTitle,int columnsNum){
		Sheet sheet = workbook.createSheet();
		//自动设置宽度,excel 2007效率低
//		if(sheet instanceof SXSSFSheet){
//			((SXSSFSheet)sheet).trackAllColumnsForAutoSizing();
//		}
		int rowIndex = 0;
		if(EmptyUtils.isNotEmpty(title)){
			//设置主题
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (columnsNum-1)));
			Row row = sheet.createRow(rowIndex++);
			Cell cell = row.createCell(0);
			cell.setCellValue(title);
			CellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short)18);
			font.setFontName("新宋体");
			cellStyle.setFont(font);
			cell.setCellStyle(cellStyle);
			for(int i=1,len=rowTitle.size(); i<len; i++){
				row.createCell(i).setCellStyle(cellStyle);
			}
		}
		if(EmptyUtils.isNotEmpty(rowTitle)){
			//设置行标题
			Row row = sheet.createRow(rowIndex++);
			int index = 0;
			for(String t : rowTitle){
				Cell cell = row.createCell(index++);
				cell.setCellValue(t);
				CellStyle cellStyle = workbook.createCellStyle();
				cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
				cellStyle.setAlignment(HorizontalAlignment.CENTER);
				Font font = workbook.createFont();
				font.setFontHeightInPoints((short)12);
				font.setFontName("新宋体");
				cellStyle.setFont(font);
				cell.setCellStyle(cellStyle);
			}
		}
		CellStyle cellStyle = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setFontName("新宋体");
		cellStyle.setFont(font);
		for(int i=0;i<columnsNum; i++){
			sheet.setDefaultColumnStyle(i, cellStyle);
		}
		return sheet;
	}

    /**
     * @describe: 向单元格中设置内容
     * @author: hcl  
     * @date: 2018/6/22 17:33
     * @param: [obj, cell, excelRowTitle]  
     * @return void  
     */
	private static void setCellValue(Object obj,Cell cell,ExcelRowTitle excelRowTitle){
        if(EmptyUtils.isNotEmpty(obj)){
            if(EmptyUtils.isNotEmpty(excelRowTitle) && EmptyUtils.isNotEmpty(excelRowTitle.getValueReplace())){
                obj = excelRowTitle.getValueReplace().replace(obj);
            }
            if(EmptyUtils.isNotEmpty(obj)){
				if(obj instanceof Long ){
					cell.setCellValue((long)obj);
				}else if(obj instanceof Integer){
					cell.setCellValue((int)obj);
				}else if(obj instanceof Short){
					cell.setCellValue((short)obj);
				}else if(obj instanceof Byte){
					cell.setCellValue((byte)obj);
				}else if(obj instanceof Float){
					cell.setCellValue((float)obj);
				}else if(obj instanceof Double){
					cell.setCellValue((double)obj);
				}else if(obj instanceof Date){
					cell.setCellValue((Date)obj);
				}else if(obj instanceof Boolean){
					cell.setCellValue((boolean)obj);
				}else{
					String str = obj.toString();
					if(str.length() > CELL_MAX_LENGTH){
						str = str.substring(0,CELL_MAX_LENGTH);
					}
					cell.setCellValue(str);
				}
			}
        }

    }

	/**
	 * 行对象
	 * @author hcl
	 *
	 */
	public static class ExcelRowTitle{
		/**
		 * 行主题唯一主键
		 */
		private String titleKey = null;
		/**
		 * 行主题名称
		 */
		private String titleName = null;

        private ValueReplace valueReplace = null;

		public ExcelRowTitle(String titleKey,String titleName){
			this.titleKey = titleKey;
			this.titleName = titleName;
		}

        public ExcelRowTitle(String titleKey,String titleName,ValueReplace valueReplace){
            this.titleKey = titleKey;
            this.titleName = titleName;
            this.valueReplace = valueReplace;
        }

		public String getTitleKey() {
			return titleKey;
		}
		public void setTitleKey(String titleKey) {
			this.titleKey = titleKey;
		}
		public String getTitleName() {
			return titleName;
		}
		public void setTitleName(String titleName) {
			this.titleName = titleName;
		}
		public void setValueReplace(ValueReplace valueReplace){
            this.valueReplace = valueReplace;
        }
        public ValueReplace getValueReplace(){
            return this.valueReplace;
        }
	}


	/**
	 * @describe: 值替换接口
	 * @author: hcl  
	 * @date: 2018/6/22 17:27
	 */
	public static abstract class ValueReplace{
	    public abstract Object replace(Object val);
    }




	/**
	 * 分批次写出excel接口
	 * @author hcl
	 *
	 */
	public static interface WriteBatches{
		/**
		 * 分批读取接口
		 * @param pageNum 要获取的页,从1开始
		 * @param startNum 起始查询的数,从0开始
		 * @return 返回需要写出的数据,返回null或List.size为0停止读取
		 */
		public List<List<Object>> write(int pageNum, int startNum);
	}

	/**
	 * 分批次写出excel接口,可以根据rowTitle调整列的顺序。
	 * @author hcl
	 *
	 */
	public static interface WriteBatchesOrder{
		/**
		 * 分批读取接口
		 * @param pageNum 要获取的页,从1开始
		 * @param startNum 起始查询的数,从0开始
		 * @return 返回需要写出的数据,返回null或List.size为0停止读取
		 */
		public List<Map<String,Object>> write(int pageNum, int startNum);
	}

	/**
	 * 分批次写出excel接口,可以根据rowTitle调整列的顺序。write返回的数据必须是一个遵循了bean规范的集合
	 * @author hcl
	 *
	 */
	public static interface WriteBatchesOrderByModel{
		/**
		 * 分批读取接口
		 * @param pageNum 要获取的页,从1开始
		 * @param startNum 起始查询的数,从0开始
		 * @return 返回需要写出的数据,返回null或List.size为0停止读取,返回的数据必须是一个遵循了bean规范的集合
		 */
		public List<? extends Object> write(int pageNum, int startNum);
	}

	/**
	 * @description:  Excel类型
	 * @author: hcl
	 * @date: created in 2018/1/27 17:40
	 */
	public static enum ExcelType{
		/**
		 * 导出格式xls(版本Excel 2003)
		 */
		XLS(65536),
		/**
		 * 导出格式xlsx(版本Excel 2007以上)
		 */
		XLSX(1048576);

		/**
		 * @description: 最大的行数
		 */
		private int maxRowNum;
		ExcelType(int maxRowNum){
			this.maxRowNum = maxRowNum;
		}
		public int getMaxRowNum(){
			return this.maxRowNum;
		}
	}
}



